package com.hnkywl.sts.dao.cwgl;

import com.hnkywl.sts.dao.sys.YgDao;
import com.hnkywl.sts.dao.xsgl.KhDao;
import com.hnkywl.sts.entity.cggl.Jsd;
import com.hnkywl.sts.entity.organ.Ck;
import com.hnkywl.sts.entity.organ.Gsjg;
import com.hnkywl.sts.entity.organ.Nbgs;
import com.hnkywl.sts.entity.sfgl.Sfk;
import com.hnkywl.sts.entity.xsgl.Xsd;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.sql.Date;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-7-7
 * Time: 上午11:12
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class YwcbDao  extends SimpleHibernateTemplate<Xsd, Long>  {
    private YgDao ygDao;
    private KhDao khDao;

    public Page<Map> findYwcbList(String xsdh, java.util.Date fhsjStart, java.util.Date fhsjEnd, Long xshtId, Long khId, Long gcId, Long sbgsId, Long sbbmId, Long sbrId, String zt, String cxzt, String sftzd, String xslx, Boolean dlkhxsd, int pageNo, int pageSize) throws ParseException {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsbm", "xsbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsr", "xsr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("xssl").as("xssl"))
                                .add(Property.forName("xsjs").as("xsjs"))
                                .add(Property.forName("orderId").as("orderId"))
                                .add(Property.forName("xsje").as("xsje"))
                                .add(Property.forName("glfyje").as("glfyje"))
                                .add(Property.forName("xsbhsje").as("xsbhsje"))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xsht.id").as("xsht_id"))
                                .add(Projections.sqlProjection("xsdh as xsdh", new String[]{"xsdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("xslx as xslx", new String[]{"xslx"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("fhsj as fhsj", new String[]{"fhsj"}, new Type[]{Hibernate.TIMESTAMP}))
                                .add(Projections.sqlProjection("this_.date_created as sxsj", new String[]{"sxsj"}, new Type[]{Hibernate.TIMESTAMP}))
                                .add(Projections.sqlProjection("sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.BOOLEAN}))
                                .add(Property.forName("cwsh").as("cwsh"))
                                .add(Property.forName("kh.qc").as("kh_qc"))
                                .add(Property.forName("kh.jc").as("kh_jc"))
                                .add(Property.forName("gc.mc").as("gc_mc"))
                                .add(Property.forName("thck.mc").as("thck_mc"))
                                .add(Property.forName("fyzr.z").as("fyzr_z"))
                                .add(Property.forName("dsyfje").as("dsyfje"))
                                .add(Property.forName("xsgs.qc").as("xsgs_qc"))
                                .add(Property.forName("xsgs.jc").as("xsgs_jc"))
                                .add(Property.forName("xsbm.qc").as("xsbm_qc"))
                                .add(Property.forName("xsbm.jc").as("xsbm_jc"))
                                .add(Property.forName("xsr.xm").as("xsr_xm"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.jc").as("sbbm_jc"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Projections.sqlProjection("yck as yck", new String[]{"yck"}, new Type[]{Hibernate.BOOLEAN}))
                                .add(Property.forName("insertFlag").as("insertFlag"))
                                .add(Property.forName("deleteFlag").as("deleteFlag"))
                                .add(Property.forName("tzsqzt").as("tzsqzt"))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xsdh)) {
            criteria.add(Property.forName("xsdh").like(xsdh, MatchMode.ANYWHERE));
        }

        if (fhsjStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sDaysAfterNowDate(fhsjStart)));
        } else {
            java.util.Date start = sDaysAfterNowDate(new java.util.Date());
            criteria.add(Restrictions.ge("dateCreated", start));
        }
        if (fhsjEnd != null) {
            criteria.add(Restrictions.le("dateCreated", jDaysAfterNowDate(fhsjEnd)));
        } else {
            java.util.Date end = jDaysAfterNowDate(new java.util.Date());
            criteria.add(Restrictions.le("dateCreated", end));
        }

        if (StringUtils.isNotEmpty(cxzt)) {
            if ("wcx".equals(cxzt)) {
                criteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
            } else if ("ycx".equals(cxzt)) {
                criteria.add(Restrictions.eq("sftzd", true));
            }
        }

        if (StringUtils.isNotEmpty(zt)) {
            if (zt != null && !zt.isEmpty() && "ybc".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            } else if (zt != null && !zt.isEmpty() && "ytj".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            } else if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            }
        }
        if (xshtId != null) {
            criteria.add(Restrictions.eq("xsht.id", xshtId));
        }

            criteria.add(Restrictions.eq("yck", true));

        if (sbgsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", sbgsId));
        }
        if (sbbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", sbbmId));
        }
        if (sbrId != null) {
            criteria.add(Restrictions.eq("sbr.id", sbrId));
        }
        if (khId != null) {
            criteria.add(Restrictions.eq("kh.id", khId));
        }
        if (gcId != null) {
            criteria.add(Restrictions.eq("gc.id", gcId));
        }

        if (sftzd != null) {
            criteria.add(Restrictions.ne("xslx", "nbxs"));
            criteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
        } else {
            criteria.add(Restrictions.eq("zt", "ysp"));
            criteria.add(Restrictions.or(Restrictions.isNull("yjs"), Restrictions.eq("yjs", false)));
        }

        if (StringUtils.isNotEmpty(xslx)) {
            criteria.add(Restrictions.eq("xslx", xslx));
        }

        if (dlkhxsd!=null && dlkhxsd) {
            DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Nbgs.class);
            detachedCriteria
                    .setProjection(Projections.projectionList()
                            .add(Property.forName("id").as("id")));
            criteria.add(Property.forName("sbgs.id").in(detachedCriteria));
        }

        createXsdDataRegion(criteria);

        criteria.addOrder(Property.forName("dateCreated").desc());
        criteria.addOrder(Property.forName("id").desc());
        criteria.addOrder(Property.forName("yxsd.id").desc());


        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    //    销售单合计
    public List<Map> findSumYwcb(String xsdh, java.util.Date fhsjStart, java.util.Date fhsjEnd, Long xshtId, Long khId, Long gcId, Long sbgsId, Long sbbmId, Long sbrId, String zt, String cxzt, String sftzd, String xslx, Boolean dlkhxsd) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlbase = "select sum(xsjs) as \"xsjs\",sum(xssl) as \"xssl\",sum(xsje) as \"xsje\",sum(gylfyje) as \"gylfyje\" from (";

        String sql1 = "select sum(xsmx.js) as xsjs,sum(xsmx.sbsl) as xssl, sum(xsmx.hsj * xsmx.ck_sbsl) as xsje," +
                "0 as gylfyje from t_biz_xsd t " +
                " left outer join t_biz_xsmx xsmx on t.id = xsmx.xsd_id and xsmx.deleted!=1";

        String sql2 = "select 0 as xsjs,0 as xssl, 0 as xsje,sum(fy.je) as gylfyje from t_biz_xsd t " +
                " left outer join t_biz_glfy fy on t.ID = fy.xsd_id and fy.deleted!=1";


        if (StringUtils.isNotEmpty(xsdh)) {
            condition.add(" t.xsdh like :xsdh ");
            param.put("xsdh", "%" + xsdh + "%");
        }

        if (fhsjStart != null) {
            condition.add(" t.date_created>= :fhsjStart ");
            param.put("fhsjStart", sDaysAfterNowDate(fhsjStart));
        } else {
            java.util.Date start = sDaysAfterNowDate(new java.util.Date());
            condition.add(" t.date_created>= :fhsjStart ");
            param.put("fhsjStart", start);
        }
        if (fhsjEnd != null) {
            condition.add(" t.date_created<= :fhsjEnd ");
            param.put("fhsjEnd", jDaysAfterNowDate(fhsjEnd));
        } else {
            java.util.Date end = jDaysAfterNowDate(new java.util.Date());
            condition.add(" t.date_created<= :fhsjEnd ");
            param.put("fhsjEnd", end);
        }
        if (cxzt != null && !cxzt.isEmpty() && "wcx".equals(cxzt)) {
            condition.add(" (t.sftzd is null or t.sftzd=0) ");
        } else if (cxzt != null && !cxzt.isEmpty() && "ycx".equals(cxzt)) {
            condition.add(" t.sftzd=1 ");
        }

        if (zt != null && !zt.isEmpty() && "ybc".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        } else if (zt != null && !zt.isEmpty() && "ytj".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        } else if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        }
        if (xshtId != null) {
            condition.add(" t.xsht_id=:xsht_id");
            param.put("xsht_id", xshtId);
        }

        if (sbgsId != null) {
            condition.add(" t.sbgs_id=:sbgs_id ");
            param.put("sbgs_id", sbgsId);
        }
        if (sbbmId != null) {
            condition.add(" t.sbbm_id=:sbbm_id ");
            param.put("sbbm_id", sbbmId);
        }
        if (sbrId != null) {
            condition.add(" t.sbr_id=:sbr_id ");
            param.put("sbr_id", sbrId);
        }

        if (khId != null) {
            condition.add(" t.kh_id=:kh_id ");
            param.put("kh_id", khId);
        }

        if (gcId != null) {
            condition.add(" t.gc_id=:gc_id ");
            param.put("gc_id", gcId);
        }

        if (StringUtils.isNotEmpty(xslx)) {
            condition.add(" t.xslx=:xslx");
            param.put("xslx", xslx);
        } else {
            condition.add(" t.xslx!='nbxs'");
        }

        if (sftzd != null) {
            condition.add(" t.sftzd=0");
        } else {
            condition.add(" t.zt='ysp' and (t.yjs is null or t.yjs=0)");
        }

        if (dlkhxsd!=null && dlkhxsd) {
            condition.add(" t.sbgs_id in (select id from T_SYS_GSJG where type='nbgs' and deleted!=1) ");
        }

        String dataRegion = createSQLDataRegion(null, "t");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql1 += " where t.deleted!=1 and " + whereSql + dataRegion;
        sql2 += " where t.deleted!=1 and " + whereSql + dataRegion;

        String sql = sqlbase + sql1 + " union all " + sql2 + ")";

        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }


    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public java.util.Date sDaysAfterNowDate(java.util.Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }


    //字符串转化成日期并且指定是0点0时0分
    private java.util.Date praseStrToDate(String str) {
        java.util.Date date = null;
        DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
        try {
            date = format1.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        return date;
    }

    //获取明天的 0点0时0分
    private java.util.Date getNextDay(java.util.Date time) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(time);
        calendar.add(Calendar.DAY_OF_YEAR, 1);
        java.util.Date date = calendar.getTime();
        return date;
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public java.util.Date jDaysAfterNowDate(java.util.Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    private void createXsdDataRegion(Criteria criteria) {
        criteria.createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN);
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    break;
                case 1:
                    criteria.add(Restrictions.in("thck", list));
                    break;
                case 2:
                    criteria.add(Restrictions.in("thck", list));
                    break;
                case 3:
                    criteria.add(Restrictions.in("thck", list));
                    break;
            }
        } else {


            SimpleExpression se = null;
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    return;
                case 1:
                    se = Restrictions.eq("createOrgan.id", YgService.getCurrentYgZgGsId());
                    break;
                case 2:
                    se = Restrictions.eq("createDepartment.id", YgService.getCurrentYgGsId());
                    break;
                case 3:
                    se = Restrictions.eq("createUser.id", YgService.getCurrentYg().getId());
                    break;
            }

            criteria.add(
                    Restrictions.or(se,
                            Restrictions.and(Restrictions.eq("sbgs.id", YgService.getCurrentYgZgGsId()),
                                    Restrictions.eq("zt", "ysp")
                            )
                    )
            );
        }
    }


    private String createSQLDataRegion(String joinStr, String alias) {
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            String ids = "";
            for (Ck ck : list) {
                ids += ck.getId() + ",";
            }
            ids = ids.substring(0, ids.length() - 1);


            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 2:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 3:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
            }
            return regionSql.replace("{0}", joinStr).replace("{1}", alias);
        } else {
            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ( {1}CREATEORGAN_ID =" + YgService.getCurrentYgZgGsId();
                    break;
                case 2:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    break;
                case 3:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    regionSql += "  and   {1}createuser_id =" + YgService.getCurrentYg().getId();
                    break;
            }

            if (YgService.getCurrentYgCxfw() != 0) {
                regionSql += " or {1}sbgs_id =" + YgService.getCurrentYgZgGsId() + ")";
            }

            return regionSql.replace("{0}", joinStr).replace("{1}", alias);
        }
    }

    public List<Map> getVoucher(Xsd xsd) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call VOUCHER_YWCB(?,?)}");
            call.setLong(1, xsd.getId());
            call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(2);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            List<Map> result = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                result.add(r);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }


    @Autowired
    public void setYgDao(YgDao ygDao) {
        this.ygDao = ygDao;
    }

    @Autowired
    public void setKhDao(KhDao khDao) {
        this.khDao = khDao;
    }

}
